package dao.impl;

import bean.Grade;
import bean.Student;
import bean.StudentStateEnum;
import dao.StudentDao;
import utils.DruidUtils;
import utils.PageUtils;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class StudentDaoImpl extends DruidUtils implements StudentDao {


    @Override
    public List<Student> getStudents(String stuname, String stuno, int sex,int pageidex,int pagesize) {

        ArrayList students = null;
        try {
            String sql = "select * from student where state!=4";
            StringBuffer sb = new StringBuffer(sql);
            ArrayList list = new ArrayList();
            if (stuname!=null&&stuname.length()!=0){
                sb.append(" and stuname like ?");
                list.add("%"+stuname+"%");
            }
            if (stuno!=null&&stuno.length()!=0){
                sb.append(" and stuno = ?");
                list.add(stuno);
            }
            if (sex!=-1){
                sb.append(" and sex =?");
                list.add(sex);
            }
            sb.append(" limit ?,?");
            list.add(pageidex);
            list.add(pagesize);
            rs=query(sb.toString(),list);
            students = new ArrayList();
            while (rs.next()){
                Student student = new Student() ;
                student.setStuid(rs.getInt("stuid"));
                student.setStuname(rs.getString("stuname"));
                student.setStuno(rs.getString("stuno"));
                student.setSex(rs.getInt("sex"));
                student.setPhone(rs.getString("phone"));
                student.setEmail(rs.getString("email"));
                student.setRegistered(rs.getString("registered"));
                student.setAddress(rs.getString("address"));
                student.setProfession(rs.getString("profession"));
                student.setIdnumber(rs.getString("idnumber"));
                student.setPolitics(rs.getString("politics"));
                student.setRegdate(rs.getDate("regdate"));
                student.setState(rs.getInt("state"));
                student.setIntroduction(rs.getString("introduction"));
                student.setGid(rs.getInt("gid"));
                students.add(student);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeall();
        }

        return students;
    }

    @Override
    public int getTotal(String stuname, String stuno, int sex) {
        int i= 0;
        try {
            String sql = "select count(*) from student where state!=4";
            StringBuffer sb = new StringBuffer(sql);
            ArrayList list = new ArrayList();
            if (stuname!=null&&stuname.length()!=0){
                sb.append(" and stuname like ?");
                list.add("%"+stuname+"%");
            }
            if (stuno!=null&&stuno.length()!=0){
                sb.append(" and stuno = ?");
                list.add(stuno);
            }
            if (sex!=-1){
                sb.append(" and sex =?");
                list.add(sex);
            }
            rs= query(sb.toString(),list);
            i = 0;
            if (rs.next()){
              i = rs.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeall();
        }

        return i;
    }

    @Override
    public int addstu(Student student) {
        String sql ="insert into student values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        ArrayList list = new ArrayList();
        list.add(null);
        list.add(student.getStuname());
        list.add(student.getStuno());
        list.add(student.getSex());
        list.add(student.getPhone());
        list.add(student.getEmail());
        list.add(student.getRegistered());
        list.add(student.getAddress());
        list.add(student.getProfession());
        list.add(student.getIdnumber());
        list.add(student.getPolitics());
        list.add(new Date());
        list.add(1);
        list.add(student.getIntroduction());
        list.add(student.getGid());
        k=update(sql,list);
        closeall();
        return k;



    }

    @Override
    public Student findById(int stuid) {
        String sql = "select * from student where state!=4 and stuid =?";
        ArrayList list = new ArrayList();
        list.add(stuid);
        rs=query(sql,list);
        Student student = new Student();
        try {
            while (rs.next()){
                student.setStuid(rs.getInt("stuid"));
                student.setStuname(rs.getString("stuname"));
                student.setStuno(rs.getString("stuno"));
                student.setSex(rs.getInt("sex"));
                student.setPhone(rs.getString("phone"));
                student.setEmail(rs.getString("email"));
                student.setRegistered(rs.getString("registered"));
                student.setAddress(rs.getString("address"));
                student.setProfession(rs.getString("profession"));
                student.setIdnumber(rs.getString("idnumber"));
                student.setPolitics(rs.getString("politics"));
                student.setRegdate(rs.getDate("regdate"));
                student.setState(rs.getInt("state"));
                student.setIntroduction(rs.getString("introduction"));
                student.setGid(rs.getInt("gid"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
        }
        return student;
    }

    @Override
    public int updateStudent(Student student) {

            String sql ="update student set stuname=?,stuno=?,sex=?,phone=?,email=?,registered=?,address=?,profession=?,idnumber=?,politics=?,introduction=?,gid=? where stuid =?";
            ArrayList list = new ArrayList();
            list.add(student.getStuname());
            list.add(student.getStuno());
            list.add(student.getSex());
            list.add(student.getPhone());
            list.add(student.getEmail());
            list.add(student.getRegistered());
            list.add(student.getAddress());
            list.add(student.getProfession());
            list.add(student.getIdnumber());
            list.add(student.getPolitics());
            list.add(student.getIntroduction());
            list.add(student.getGid());
            list.add(student.getStuid());
            k=update(sql,list);
            closeall();
            return k;
    }

    @Override
    public int deleteStudent(int stuid) {
        String sql ="update student set state = ? where stuid =? ";
        ArrayList list = new ArrayList();
        list.add(StudentStateEnum.DELETE.type);
        list.add(stuid);
        k=update(sql,list);
        closeall();
        return k;
    }

    @Override
    public int findByStuno(int stuno) {
        String sql = "select * from student where stuno =?";
        ArrayList list = new ArrayList();
        list.add(stuno);
        rs=query(sql,list);
        try {
            if (rs.next()){
                return 1;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeall();
        }
        return 0;
    }
}
